begin;
-- Table: win_ecosystems

-- DROP TABLE win_ecosystems;

CREATE TABLE win_ecosystems
(
  id serial NOT NULL,
  ecosystem_type character varying NOT NULL,
  parent_id bigint_unsigned,
  CONSTRAINT "PKey_win_ecosystems" PRIMARY KEY (id)
);

insert into win_ecosystems (ecosystem_type) values ('Forest');
insert into win_ecosystems (ecosystem_type) values ('Savanna');
insert into win_ecosystems (ecosystem_type) values ('Shrubland');
insert into win_ecosystems (ecosystem_type) values ('Grassland');
insert into win_ecosystems (ecosystem_type) values ('Wetlands (inland)');
insert into win_ecosystems (ecosystem_type) values ('Rocky Areas [e.g. inland cliffs, mountain peaks]');
insert into win_ecosystems (ecosystem_type) values ('Caves and Subterranean Habitats (non-aquatic)');
insert into win_ecosystems (ecosystem_type) values ('Desert');
insert into win_ecosystems (ecosystem_type) values ('Sea');
insert into win_ecosystems (ecosystem_type) values ('Coastline');
insert into win_ecosystems (ecosystem_type) values ('Artificial - Terrestrial');
insert into win_ecosystems (ecosystem_type) values ('Artificial - Aquatic');
insert into win_ecosystems (ecosystem_type) values ('Introduced Vegetation');
insert into win_ecosystems (ecosystem_type) values ('Other');
insert into win_ecosystems (ecosystem_type) values ('Unknown');

insert into win_ecosystems(ecosystem_type, parent_id) values ('Boreal', 1);
insert into win_ecosystems(ecosystem_type, parent_id) values ('Subarctic', 1);
insert into win_ecosystems(ecosystem_type, parent_id) values ('Subantarctic', 1);
insert into win_ecosystems(ecosystem_type, parent_id) values ('Temperate', 1);
insert into win_ecosystems(ecosystem_type, parent_id) values ('Subtropical/Tropical Dry', 1);
insert into win_ecosystems(ecosystem_type, parent_id) values ('Subtropical/Tropical Moist Lowland', 1);
insert into win_ecosystems(ecosystem_type, parent_id) values ('Subtropical/Tropical Mangrove', 1);
insert into win_ecosystems(ecosystem_type, parent_id) values ('Subtropical/Tropical Swamp', 1);
insert into win_ecosystems(ecosystem_type, parent_id) values ('Subtropical/Tropical Moist Montane', 1);

insert into win_ecosystems(ecosystem_type, parent_id) values ('Dry', 2);
insert into win_ecosystems(ecosystem_type, parent_id) values ('Moist', 2);

insert into win_ecosystems(ecosystem_type, parent_id) values ('Subarctic', 3);
insert into win_ecosystems(ecosystem_type, parent_id) values ('Subantarctic', 3);
insert into win_ecosystems(ecosystem_type, parent_id) values ('Boreal', 3);
insert into win_ecosystems(ecosystem_type, parent_id) values ('Temperate', 3);
insert into win_ecosystems(ecosystem_type, parent_id) values ('Subtropical/Tropical Dry', 3);
insert into win_ecosystems(ecosystem_type, parent_id) values ('Subtropical/Tropical Moist', 3);
insert into win_ecosystems(ecosystem_type, parent_id) values ('Subtropical/Tropical High Altitude', 3);
insert into win_ecosystems(ecosystem_type, parent_id) values ('Mediterranean-type Shrubby Vegetation', 3);

insert into win_ecosystems(ecosystem_type, parent_id) values ('Tundra', 4);
insert into win_ecosystems(ecosystem_type, parent_id) values ('Subarctic', 4);
insert into win_ecosystems(ecosystem_type, parent_id) values ('Subantarctic', 4);
insert into win_ecosystems(ecosystem_type, parent_id) values ('Temperate', 4);
insert into win_ecosystems(ecosystem_type, parent_id) values ('Subtropical/Tropical Dry Lowland', 4);
insert into win_ecosystems(ecosystem_type, parent_id) values ('Subtropical/Tropical Seasonally Wet/Flooded Lowland', 4);
insert into win_ecosystems(ecosystem_type, parent_id) values ('Subtropical/Tropical High Altitude', 4);

insert into win_ecosystems(ecosystem_type, parent_id) values ('Permanent Rivers/Streams/Creeks [includes waterfalls]', 5);
insert into win_ecosystems(ecosystem_type, parent_id) values ('Seasonal/Intermittent/Irregular Rivers/Streams/Creeks', 5);
insert into win_ecosystems(ecosystem_type, parent_id) values ('Shrub Dominated Wetlands', 5);
insert into win_ecosystems(ecosystem_type, parent_id) values ('Bogs, Marshes, Swamps, Fens, Peatlands', 5);
insert into win_ecosystems(ecosystem_type, parent_id) values ('Permanent Freshwater Lakes [over 8 ha]', 5);
insert into win_ecosystems(ecosystem_type, parent_id) values ('Seasonal/Intermittent Freshwater Lakes [over 8 ha]', 5);
insert into win_ecosystems(ecosystem_type, parent_id) values ('Permanent Freshwater Marshes/Pools [under 8 ha]', 5);
insert into win_ecosystems(ecosystem_type, parent_id) values ('Seasonal/Intermittent Freshwater Marshes/Pools [under 8 ha]', 5);
insert into win_ecosystems(ecosystem_type, parent_id) values ('Freshwater Springs and Oases', 5);
insert into win_ecosystems(ecosystem_type, parent_id) values ('Tundra Wetlands [includes pools and temporary waters from snowmelt]', 5);
insert into win_ecosystems(ecosystem_type, parent_id) values ('Alpine Wetlands [includes temporary waters from snowmelt]', 5);
insert into win_ecosystems(ecosystem_type, parent_id) values ('Geothermal Wetlands', 5);
insert into win_ecosystems(ecosystem_type, parent_id) values ('Permanent Inland Deltas', 5);
insert into win_ecosystems(ecosystem_type, parent_id) values ('Permanent Saline, Brackish or Alkaline Lakes', 5);
insert into win_ecosystems(ecosystem_type, parent_id) values ('Seasonal/Intermittent Saline, Brackish or Alkaline Lakes and Flats', 5);
insert into win_ecosystems(ecosystem_type, parent_id) values ('Permanent Saline, Brackish or Alkaline Marshes/Pools', 5);
insert into win_ecosystems(ecosystem_type, parent_id) values ('Seasonal/Intermittent Saline, Brackish or Alkaline Marshes/Pools', 5);
insert into win_ecosystems(ecosystem_type, parent_id) values ('Karst and Other Subterranean Hydrological Systems [inland]', 5);

insert into win_ecosystems(ecosystem_type, parent_id) values ('Caves', 7);
insert into win_ecosystems(ecosystem_type, parent_id) values ('Other Subterranean Habitats', 7);

insert into win_ecosystems(ecosystem_type, parent_id) values ('Hot', 8);
insert into win_ecosystems(ecosystem_type, parent_id) values ('Temperate', 8);
insert into win_ecosystems(ecosystem_type, parent_id) values ('Cold', 8);

insert into win_ecosystems(ecosystem_type, parent_id) values ('Open', 9);
insert into win_ecosystems(ecosystem_type, parent_id) values ('Shallow [usually less than 6 m deep at low tide; includes sea bays and straits]', 9);
insert into win_ecosystems(ecosystem_type, parent_id) values ('Subtidal Aquatic Beds [kelp beds, sea- grass beds and tropical marine meadows]', 9);
insert into win_ecosystems(ecosystem_type, parent_id) values ('Coral Reefs', 9);

insert into win_ecosystems(ecosystem_type, parent_id) values ('Rocky Shores [includes rocky offshore islands and sea cliffs]', 10);
insert into win_ecosystems(ecosystem_type, parent_id) values ('Sand, Shingle or Pebble Shores [includes sand bars, spits, sandy islets, dune systems]', 10);
insert into win_ecosystems(ecosystem_type, parent_id) values ('Estuarine Waters', 10);
insert into win_ecosystems(ecosystem_type, parent_id) values ('Intertidal Mud, Sand or Salt Flats', 10);
insert into win_ecosystems(ecosystem_type, parent_id) values ('Intertidal Marshes [includes salt marshes]', 10);
insert into win_ecosystems(ecosystem_type, parent_id) values ('Coastal Brackish/Saline Lagoons', 10);
insert into win_ecosystems(ecosystem_type, parent_id) values ('Coastal Freshwater Lagoons', 10);
insert into win_ecosystems(ecosystem_type, parent_id) values ('Karst and Other Subterranean Hydrological Systems [marine/coastal]', 10);

insert into win_ecosystems(ecosystem_type, parent_id) values ('Arable Land', 11);
insert into win_ecosystems(ecosystem_type, parent_id) values ('Pastureland', 11);
insert into win_ecosystems(ecosystem_type, parent_id) values ('Plantations', 11);
insert into win_ecosystems(ecosystem_type, parent_id) values ('Rural Gardens', 11);
insert into win_ecosystems(ecosystem_type, parent_id) values ('Urban Areas', 11);
insert into win_ecosystems(ecosystem_type, parent_id) values ('Subtropical/Tropical Heavily Degraded Former Forest', 11);

insert into win_ecosystems(ecosystem_type, parent_id) values ('Water Storage Areas (over  8 ha)', 12);
insert into win_ecosystems(ecosystem_type, parent_id) values ('Ponds (below 8 ha)', 12);
insert into win_ecosystems(ecosystem_type, parent_id) values ('Aquaculture Ponds', 12);
insert into win_ecosystems(ecosystem_type, parent_id) values ('Salt Exploitation Sites', 12);
insert into win_ecosystems(ecosystem_type, parent_id) values ('Excavations (open)', 12);
insert into win_ecosystems(ecosystem_type, parent_id) values ('Wastewater Treatment Areas', 12);
insert into win_ecosystems(ecosystem_type, parent_id) values ('Irrigated Land [includes irrigation channels]', 12);
insert into win_ecosystems(ecosystem_type, parent_id) values ('Seasonally Flooded Agricultural Land', 12);
insert into win_ecosystems(ecosystem_type, parent_id) values ('Canals and Drainage Channels, Ditches', 12);
insert into win_ecosystems(ecosystem_type, parent_id) values ('Karst and Other Subterranean Hydrological Systems [human-made]', 12);

ALTER TABLE win_observations ADD COLUMN ecosystem_id bigint_unsigned NOT NULL DEFAULT 15;
ALTER TABLE win_observations ADD CONSTRAINT "FKey_win_observations_win_ecosystems" FOREIGN KEY (ecosystem_id) REFERENCES win_ecosystems (id)    ON UPDATE NO ACTION ON DELETE NO ACTION;

commit;